<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Java👉JDBC | 小李博客</title><meta name="keywords" content="JavaSE"><meta name="author" content="小李博客"><meta name="copyright" content="小李博客"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="数据库连接数据库概述  数据库(DataBase,DB):指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。 数据库管理系统(DataBase Management System，DBMS) :指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。">
<meta property="og:type" content="article">
<meta property="og:title" content="Java👉JDBC">
<meta property="og:url" content="http://xiaoliblog.cn/page/java03.html">
<meta property="og:site_name" content="小李博客">
<meta property="og:description" content="数据库连接数据库概述  数据库(DataBase,DB):指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。 数据库管理系统(DataBase Management System，DBMS) :指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png">
<meta property="article:published_time" content="2021-01-05T10:00:16.163Z">
<meta property="article:modified_time" content="2021-03-20T10:10:33.085Z">
<meta property="article:author" content="小李博客">
<meta property="article:tag" content="JavaSE">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png"><link rel="shortcut icon" href="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png"><link rel="canonical" href="http://xiaoliblog.cn/page/java03"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.js" defer></script><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: {"appId":"RTG4CPNBLJ","apiKey":"f1745bdad68ceec57653b78244fe332c","indexName":"MyBlogIndex","hits":{"per_page":6},"languages":{"input_placeholder":"搜索文章","hits_empty":"找不到您查询的内容：${query}","hits_stats":"找到 ${hits} 条结果，用时 ${time} 毫秒"}},
  localSearch: undefined,
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":200},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: {"limitCount":100,"languages":{"author":"作者: 小李博客","link":"链接: ","source":"来源: 小李博客","info":"著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。"}},
  lightbox: 'mediumZoom',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"top-center"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: true
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-03-20 18:10:33'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const fontSizeVal = saveToLocal.get('global-font-size')
    if (fontSizeVal !== undefined) {
      document.documentElement.style.setProperty('--global-font-size', fontSizeVal + 'px')
    }
    })(window)</script><link rel="stylesheet" href="/css/MyStyle/MyStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="/css/MyStyle/tagStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/zykjofficial/zykjresource@master/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/plugins.min.css" media="defer" onload="this.media='all'"><meta name="generator" content="Hexo 5.2.0"><link rel="alternate" href="/atom.xml" title="小李博客" type="application/atom+xml">
</head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">小李博客</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">Java👉JDBC</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-01-05T10:00:16.163Z" title="发表于 2021-01-05 18:00:16">2021-01-05</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-03-20T10:10:33.085Z" title="更新于 2021-03-20 18:10:33">2021-03-20</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/Java%E5%90%8E%E7%AB%AF/">Java后端</a><i class="fas fa-angle-right post-meta-separator"></i><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/Java%E5%90%8E%E7%AB%AF/JavaSE/">JavaSE</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">5.9k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>22分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="Java👉JDBC"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div><article class="post-content" id="article-container"><h1 id="数据库连接"><a href="#数据库连接" class="headerlink" title="数据库连接"></a>数据库连接</h1><h2 id="数据库概述"><a href="#数据库概述" class="headerlink" title="数据库概述"></a>数据库概述</h2><blockquote>
<p> <strong><em>数据库(DataBase,DB)</em></strong>:指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。<br> <strong><em>数据库管理系统(DataBase Management System，DBMS)</em></strong> :指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。</p>
</blockquote>
<p>常见数据库管理系统</p>
<ul>
<li>MySQL<br>MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。</li>
<li>Orcale<br>Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。</li>
<li>DB2<br>DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司的需要，并可灵活地服务于中小型电子商务解决方案。</li>
<li>Microsoft SQL Server<br>SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。</li>
</ul>
<h2 id="SQL语言"><a href="#SQL语言" class="headerlink" title="SQL语言"></a>SQL语言</h2><h3 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h3><p>Data Definition Language 数据定义语言</p>
<ul>
<li>作用:用来定义数据库对象：库、表、列等</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 操作数据库 */</span></span><br><span class="line"><span class="comment">--增 create</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> 数据库名; <span class="comment">--创建新的数据库</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> 数据库名 <span class="built_in">character</span> <span class="keyword">set</span> GBK;<span class="comment">--创建新的字符集为GBK的数据库</span></span><br><span class="line"><span class="comment">--查 show</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">databases</span>;<span class="comment">--查看DBMS中所有的数据库</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">database</span> 数据库名;<span class="comment">--查看数据库的定义信息</span></span><br><span class="line"><span class="comment">--改 alter</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">database</span> 数据库名 <span class="built_in">character</span> <span class="keyword">set</span> utf8;<span class="comment">--把数据库改成utf8字符集</span></span><br><span class="line"><span class="comment">--删 drop</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">database</span> 数据库名;<span class="comment">--删除数据库</span></span><br><span class="line"><span class="comment">--其他</span></span><br><span class="line"><span class="keyword">use</span> 数据库名;<span class="comment">--使用数据库</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">database</span>();<span class="comment">--查看当前正在使用的数据库</span></span><br><span class="line"><span class="keyword">flush</span> <span class="keyword">privileges</span>;<span class="comment">--刷新数据库</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/* 操作表 */</span></span><br><span class="line"><span class="comment">--增 create</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(<span class="comment">--创建新表</span></span><br><span class="line">字段名<span class="number">1</span> 字段类型(长度) [约束],</span><br><span class="line">字段名<span class="number">2</span> 字段类型(长度) [约束],</span><br><span class="line">...</span><br><span class="line">);</span><br><span class="line"><span class="comment">--查 show</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">tables</span>;<span class="comment">--查询数据库中有的表</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> 表名;<span class="comment">--查看表格的创建细节</span></span><br><span class="line">desc 表名;<span class="comment">--查看表的字段信息					</span></span><br><span class="line"><span class="comment">--改 alter</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> 列名 数据类型[约束];<span class="comment">--添加一个列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">change</span> 旧列名 新列名 新数据类型[约束];<span class="comment">--修改列的列名</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">modify</span> 列名 新数据类型[约束];<span class="comment">--修改列的数据类型</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">drop</span> 列名;<span class="comment">--删除表的列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 AUTO_INCREMENT=<span class="number">5</span>;<span class="comment">--重新设置自增id</span></span><br><span class="line"><span class="keyword">rename</span> <span class="keyword">table</span> 旧表名 <span class="keyword">to</span> 新表名;<span class="comment">--修改表名</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="built_in">character</span> <span class="keyword">set</span> 字符集;<span class="comment">--修改表的字符集</span></span><br><span class="line"><span class="comment">--删 drop</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> 表名;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> <span class="keyword">if</span> <span class="keyword">exists</span> 表名;<span class="comment">--如果表存在,则删除;如果不存在,也不会报错.这种语句是MySql特有的语句,其他的数据库都是不支持这种语法的</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="DML"><a href="#DML" class="headerlink" title="DML"></a>DML</h3><p>Data Manipulation Language 数据操作语言</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--增 insert</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名 <span class="keyword">values</span>(字段值<span class="number">1</span>,字段值<span class="number">2</span>, ...);<span class="comment">--全字段插入</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名(字段<span class="number">1</span>) <span class="keyword">values</span>(字段值<span class="number">1</span>);<span class="comment">--部分字段插入</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名 <span class="keyword">values</span>(记录<span class="number">1</span>的所有字段),(记录<span class="number">2</span>的所有字段)...;<span class="comment">--一次性插入多条 数据</span></span><br><span class="line"><span class="comment">--改 update</span></span><br><span class="line"><span class="keyword">update</span> 表名 <span class="keyword">set</span> 列名<span class="number">1</span>=列值<span class="number">1</span>,列名<span class="number">2</span>=列值<span class="number">2</span> ... <span class="keyword">where</span> 列名=值;<span class="comment">--修改操作</span></span><br><span class="line"><span class="keyword">update</span> 表名 <span class="keyword">set</span> 列名<span class="number">1</span>=列值<span class="number">1</span>;<span class="comment">--没有where条件的,会直接修改所有列名的值</span></span><br><span class="line"><span class="keyword">update</span> 表名 <span class="keyword">set</span> 列名<span class="number">1</span> = <span class="keyword">replace</span>(列名<span class="number">1</span>,<span class="string">&#x27;原内容&#x27;</span>,<span class="string">&#x27;要替换的内容&#x27;</span>);<span class="comment">--批量替换字段里的字符串</span></span><br><span class="line"><span class="comment">--删 delete</span></span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 表名 <span class="keyword">where</span> 列名=值;<span class="comment">--删除表中条件是where的</span></span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 表名;<span class="comment">--删除表中所有数据，表结构还在，删除后的数据可以找回</span></span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> 表名;<span class="comment">--删除表中所有数据,把表直接drop掉，然后再创建一个同样的新表-删除的数据不能找回。该命令会让id重新从1开始自动递增</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h3 id="DQL"><a href="#DQL" class="headerlink" title="DQL"></a>DQL</h3><p>Data Query Language 数据查询语言</p>
<ul>
<li>作用:用来查询记录（数据）<br>查询语句的顺序<br>书写顺序:select → from →join on → where → group by → having → order by → limit<br>执行顺序:from → join on → where → group by → having → select → order by → limit</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 简单查询 */</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp;<span class="comment">--查询所有列</span></span><br><span class="line"><span class="keyword">select</span> empno,ename,job <span class="keyword">from</span> emp;<span class="comment">--查询指定的列</span></span><br><span class="line"><span class="keyword">select</span> empno <span class="keyword">as</span> <span class="string">&#x27;编号&#x27;</span>,ename <span class="keyword">as</span> <span class="string">&#x27;别名&#x27;</span> <span class="keyword">from</span> emp;<span class="comment">--给列起别名(as可以省略可以使用&quot;&quot;,&#x27;&#x27;或者不使用引号)</span></span><br><span class="line"><span class="keyword">select</span> e.empno,e.ename <span class="keyword">from</span> emp <span class="keyword">as</span> e;<span class="comment">--给表起别名,表中的字段也能通过表的别名来调用</span></span><br><span class="line"><span class="comment">/* 条件查询 */</span></span><br><span class="line"><span class="comment">--条件语句 &gt;  &lt;  &gt;=  &lt;=  !=(&lt;&gt;)  =</span></span><br><span class="line"><span class="comment">--and(&amp;&amp;)</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> deptno=<span class="number">10</span> <span class="keyword">and</span> sal&gt;<span class="number">2000</span>;<span class="comment">--可以使用&amp;&amp;带代替</span></span><br><span class="line"><span class="comment">--or(||)</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> ename=<span class="string">&#x27;smith&#x27;</span> <span class="keyword">or</span> sal=<span class="number">5000</span>;<span class="comment">--可以使用||来代替</span></span><br><span class="line"><span class="comment">--between...and</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> ename <span class="keyword">between</span> <span class="string">&#x27;A&#x27;</span> <span class="keyword">and</span> <span class="string">&#x27;F&#x27;</span>;<span class="comment">--BETWEEN AND作用于字符型的数据的时候,包含第一个边界值,但是不包含第二个边界值</span></span><br><span class="line"><span class="comment">--in(set)</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">in</span>(<span class="number">800</span>,<span class="number">1600</span>);<span class="comment">--in表示包含的意思</span></span><br><span class="line"><span class="comment">--is null  (尽量不要使用,会导致索引失效)</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> comm <span class="keyword">is</span> <span class="literal">null</span>;<span class="comment">--对于null的判断不能使用=号</span></span><br><span class="line"><span class="comment">--not</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> deptno !=<span class="number">10</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> deptno &lt;&gt;<span class="number">10</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> <span class="keyword">not</span> deptno =<span class="number">10</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> comm <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> <span class="keyword">not</span> comm <span class="keyword">is</span> <span class="literal">null</span>;</span><br><span class="line"><span class="comment">--注意</span></span><br><span class="line"><span class="comment">--对于字符型的数据,是严格区分大小写的</span></span><br><span class="line"><span class="comment">--Oracle在任何的平台上都是严格区分字符型数据大小写的</span></span><br><span class="line"><span class="comment">--MySql在Liunx/Unix平台上严格区分字符型数据大小写,但是在window平台上不区分字符型数据大小写</span></span><br><span class="line"><span class="comment">--可以使用BINARY关键字,让MySql在window平台上也严格字符字符型数据的大小写</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> <span class="built_in">binary</span> ename=<span class="string">&#x27;smith&#x27;</span>;</span><br><span class="line"><span class="comment">/* 模糊查询 */</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> ename <span class="keyword">like</span> <span class="string">&#x27;_M%&#x27;</span>;<span class="comment">-- _ ：任意一个字母 查询第二个字母是m的人</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> ename <span class="keyword">like</span> <span class="string">&#x27;%m%&#x27;</span>;<span class="comment">-- %：任意0~n个字母 查询名字包含m的人		</span></span><br><span class="line"><span class="comment">/* 字段控制查询 */</span>		</span><br><span class="line"><span class="keyword">select</span> salary*<span class="number">12</span> <span class="keyword">as</span> <span class="string">&#x27;年薪&#x27;</span> <span class="keyword">from</span> emp;<span class="comment">--可以使用算术表达式将月薪以年薪的形式显示</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> deptno <span class="keyword">from</span> emp;<span class="comment">--去掉重复值查询</span></span><br><span class="line"><span class="keyword">select</span> *,sal+<span class="keyword">ifnull</span>(comm,<span class="number">0</span>) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">/* 排序 */</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">asc</span>;<span class="comment">--升序排序,可默认不写</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span>;<span class="comment">--降序排序</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span>,empno <span class="keyword">asc</span>;<span class="comment">--按月薪降序排序，如果月薪相同，按编号升序排序</span></span><br><span class="line"><span class="comment">/* 单行函数 */</span></span><br><span class="line"><span class="comment">--year(日期字段) 将日期格式化为年(月日时分秒同理)</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(<span class="keyword">now</span>())<span class="comment">--输出今年</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(<span class="string">&#x27;2018-01-01&#x27;</span>);<span class="comment">--2018</span></span><br><span class="line"><span class="comment">--substr(被截取字段名称,起启下标,截取长度) 截取字符串的函数</span></span><br><span class="line"><span class="keyword">select</span> ename,<span class="keyword">substr</span>(ename,<span class="number">2</span>,<span class="number">1</span>) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">--length(str) 返回字符型数据的长度</span></span><br><span class="line"><span class="keyword">select</span> ename,<span class="keyword">length</span>(ename) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> <span class="keyword">length</span>(ename)=<span class="number">5</span>;<span class="comment">--查询名字的长是5的员工</span></span><br><span class="line"><span class="comment">--lower(str) 把字符型数据转换为小写的字符</span></span><br><span class="line"><span class="keyword">select</span> ename,<span class="keyword">lower</span>(ename) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">--upper(str) 把字符型数据转换为大写的字符</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">where</span> <span class="built_in">binary</span> ename=<span class="keyword">upper</span>(<span class="string">&#x27;smith&#x27;</span>);</span><br><span class="line"><span class="comment">/* 聚合函数 */</span></span><br><span class="line"><span class="comment">--count() 统计指定列不为NULL的记录行数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> emp <span class="keyword">where</span> sal+<span class="keyword">ifnull</span>(comm,<span class="number">0</span>)&gt;<span class="number">2500</span></span><br><span class="line"><span class="comment">--max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">max</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">--min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">min</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">--sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sum</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">--avg() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">avg</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line"><span class="comment">/* 分组查询 */</span></span><br><span class="line"><span class="comment">--分组查询</span></span><br><span class="line"><span class="keyword">select</span> deptno,<span class="keyword">sum</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line"><span class="comment">--HAVING子句</span></span><br><span class="line"><span class="keyword">select</span> deptno,<span class="keyword">sum</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno <span class="keyword">having</span> <span class="keyword">sum</span>(sal)&gt;<span class="number">9000</span>;</span><br><span class="line">	<span class="comment">--having与where的区别:where是先筛选后分组;having是先分组后筛选</span></span><br><span class="line"><span class="comment">/* LIMIT */</span></span><br><span class="line"><span class="comment">--分页查询</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">limit</span> <span class="number">5</span>;<span class="comment">--查询5行记录，默认从0开始</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">limit</span> <span class="number">0</span>,<span class="number">5</span>;<span class="comment">--查询5行记录，起始行从0开始</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">limit</span> <span class="number">3</span>,<span class="number">10</span>;<span class="comment">--查询10行记录，起始行从3开始</span></span><br><span class="line">	<span class="comment">--注意:LIMIT只能在MySql和SqlServer中有效</span></span><br><span class="line"><span class="comment">/* 多表查询(跨表查询) */</span></span><br><span class="line"><span class="comment">--联合查询</span></span><br><span class="line">  <span class="comment">--说明:合并结果集就是把两个select语句的查询结果合并到一起,被合并的两个结果：列数、列类型必须相同</span></span><br><span class="line">  <span class="comment">--union 去除重复记录</span></span><br><span class="line">  <span class="comment">--union all 不去除重复记录</span></span><br><span class="line"><span class="comment">--连接查询</span></span><br><span class="line">  <span class="comment">--内连接</span></span><br><span class="line">    <span class="comment">--等值连接:建立在父子表关系上,用等号来连接两个表</span></span><br><span class="line">		<span class="keyword">select</span> e.empno,e.ename,e.job,e.sal,d.deptno,d.dname</span><br><span class="line">  		<span class="keyword">from</span> emp e,dept d</span><br><span class="line">  		<span class="keyword">where</span> e.deptno=d.deptno;<span class="comment">--SQL1992语法</span></span><br><span class="line">  	<span class="keyword">select</span> e.empno,e.ename,e.job,e.sal,d.deptno,d.dname</span><br><span class="line">  		<span class="keyword">from</span> emp e</span><br><span class="line">  		<span class="keyword">inner</span> <span class="keyword">join</span> dept d <span class="keyword">on</span>(e.deptno=d.deptno);<span class="comment">--SQL1999语法</span></span><br><span class="line">  	<span class="comment">--使用内连接的时候,inner可以忽略</span></span><br><span class="line">	<span class="comment">--非等值连接:两个表之间没有父子关系,用非等号来连接两个表</span></span><br><span class="line">	<span class="keyword">select</span> e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal</span><br><span class="line">  	<span class="keyword">from</span> emp e,salgrade s</span><br><span class="line">  	<span class="keyword">where</span> e.sal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal;<span class="comment">--SQL1992语法</span></span><br><span class="line">  <span class="keyword">select</span> e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal</span><br><span class="line">  	<span class="keyword">from</span> emp e</span><br><span class="line">  	<span class="keyword">join</span> salgrade s <span class="keyword">on</span> e.sal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal<span class="comment">--SQL1999语法</span></span><br><span class="line">  	<span class="comment">--自连接:使用别名将一个表虚拟成两个表(父子表),然后再这两个表上面做等值连接</span></span><br><span class="line">  	<span class="keyword">select</span> e.empno <span class="string">&#x27;员工编号&#x27;</span>,e.ename <span class="string">&#x27;员工姓名&#x27;</span>,m.empno <span class="string">&#x27;经理编号&#x27;</span>,m.ename <span class="string">&#x27;经理姓名&#x27;</span></span><br><span class="line">  		<span class="keyword">from</span> emp e,emp m</span><br><span class="line">  		<span class="keyword">where</span> e.mgr=m.empno &amp;&amp; m.empno=<span class="number">7698</span><span class="comment">--SQL1992语法</span></span><br><span class="line">  	<span class="keyword">select</span> e.empno <span class="string">&#x27;员工编号&#x27;</span>,e.ename <span class="string">&#x27;员工姓名&#x27;</span>,m.empno <span class="string">&#x27;经理编号&#x27;</span>,m.ename <span class="string">&#x27;经理姓名&#x27;</span></span><br><span class="line">  		<span class="keyword">from</span> emp e</span><br><span class="line">  		<span class="keyword">join</span> emp m <span class="keyword">on</span>(e.mgr=m.empno)</span><br><span class="line">  		<span class="keyword">where</span> m.empno=<span class="number">7698</span><span class="comment">--SQL1999语法</span></span><br><span class="line">  <span class="comment">--外连接</span></span><br><span class="line">		<span class="comment">--左外连接(LEFT OUTER JOIN):左外连接可以把左表中不满足对应关系的数据查询出来</span></span><br><span class="line">		<span class="keyword">select</span> e.empno,e.ename,e.job,e.sal,d.deptno,d.dname</span><br><span class="line">  		<span class="keyword">from</span> emp e</span><br><span class="line">  		<span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno=d.deptno;<span class="comment">--使用外连接的时候,outer可以忽略</span></span><br><span class="line">  	<span class="keyword">select</span> e.empno,e.ename,e.job,e.sal,d.deptno,d.dname</span><br><span class="line">  		<span class="keyword">from</span> dept d</span><br><span class="line">  		<span class="keyword">left</span> <span class="keyword">join</span> emp e <span class="keyword">on</span> e.deptno=d.deptno;<span class="comment">--左表的位置是相对的</span></span><br><span class="line">  	<span class="comment">--右外连接(RIGHT OUTER JOIN):右外连接可以把右表中不满足对应关系的数据查询出来</span></span><br><span class="line">  	<span class="keyword">select</span> e.empno,e.ename,e.job,e.sal,d.deptno,d.dname</span><br><span class="line">  		<span class="keyword">from</span> emp e</span><br><span class="line">  		<span class="keyword">right</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno=d.deptno;</span><br><span class="line">  	<span class="comment">--全外连接(FULL OUTER JOIN):可以把左右两个表中不满足对应关系的数据都查询出来</span></span><br><span class="line">  	<span class="keyword">select</span> e.empno,e.ename,e.job,e.sal,d.deptno,d.dname</span><br><span class="line">  		<span class="keyword">from</span> emp e</span><br><span class="line">  		<span class="keyword">full</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno=d.deptno;<span class="comment">--Oracle支持全外连接,MySql不支持全外连接</span></span><br><span class="line">  	<span class="comment">--join的更多用法见下表</span></span><br><span class="line">	<span class="comment">--子查询</span></span><br><span class="line">  	  <span class="comment">--1.用来给主查询提供查询条件或查询数据而首先执行的一个查询</span></span><br><span class="line">  	  <span class="comment">--2.主查询使用子查询的结果</span></span><br><span class="line">  	  <span class="comment">--子查询必须要放在()里面</span></span><br><span class="line">  	<span class="comment">--分类</span></span><br><span class="line">  	  <span class="comment">--出现在where中的子查询,用来给查询提供查询条件的</span></span><br><span class="line">  	  <span class="keyword">select</span> * <span class="keyword">from</span> emp</span><br><span class="line">  	  <span class="keyword">where</span> sal&gt;(<span class="keyword">select</span> <span class="keyword">max</span>(sal) <span class="keyword">from</span> emp <span class="keyword">where</span> deptno=<span class="number">30</span>);<span class="comment">--查询高于30部门所有人工资的员工信息</span></span><br><span class="line">  	  <span class="comment">--出现下from后面的子查询,用来给主查询提供查询数据的(all,any)</span></span><br><span class="line">  	  <span class="keyword">select</span> * <span class="keyword">from</span> emp</span><br><span class="line">  	  <span class="keyword">where</span> sal&gt;<span class="keyword">all</span>(<span class="keyword">select</span> sal <span class="keyword">from</span> emp);<span class="comment">--查询高于30部门所有人工资的员工信息</span></span><br><span class="line">  	  <span class="comment">--(了解)出现在查询列表中的子查询,功能类似于外连接的效果</span></span><br></pre></td></tr></table></figure>

<h3 id="常用函数"><a href="#常用函数" class="headerlink" title="常用函数"></a>常用函数</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 数据函数 */</span></span><br><span class="line"><span class="comment">-- abs() 取绝对值</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">abs</span>(<span class="number">-1</span>); <span class="comment">-- 1</span></span><br><span class="line"><span class="comment">-- round() 用于数据的四舍五入</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">round</span>(<span class="number">13.26723</span>,<span class="number">2</span>);<span class="comment">-- 结果13.27</span></span><br><span class="line"><span class="comment">--rand() 生成随机数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">rand</span>();<span class="comment">--生成一个0到1之间的随机数</span></span><br><span class="line"><span class="comment">-- ceiling() 向上取整</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">ceiling</span>(<span class="number">2.5</span>); <span class="comment">-- 3</span></span><br><span class="line"><span class="comment">-- floor() 向下取整</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">floor</span>(<span class="number">2.5</span>); <span class="comment">-- 2</span></span><br><span class="line"><span class="comment">-- sign() 符号函数: 负数返回-1,正数返回1,0返回0</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sign</span>(<span class="number">2</span>);  <span class="comment">-- 1</span></span><br><span class="line"><span class="comment">/* 字符串函数 */</span></span><br><span class="line"><span class="comment">-- char_length(str) 返回字符串包含的字符数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">char_length</span>(<span class="string">&#x27;abcdef&#x27;</span>); <span class="comment">-- 6</span></span><br><span class="line"><span class="comment">-- concat() 合并字符串,参数可以有多个</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">concat</span>(<span class="string">&#x27;我爱&#x27;</span>,<span class="string">&#x27;北京&#x27;</span>,<span class="string">&#x27;天安门&#x27;</span>); <span class="comment">-- 我爱北京天安门</span></span><br><span class="line"><span class="comment">-- insert() 替换字符串,从某个位置开始替换某个长度</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">insert</span>(<span class="string">&#x27;一二三四五六七&#x27;</span>,<span class="number">1</span>,<span class="number">3</span>,<span class="string">&#x27;八八八&#x27;</span>); <span class="comment">-- 八八八四五六七</span></span><br><span class="line"><span class="comment">-- lower() 把字符型数据转换为小写的字符</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">lower</span>(<span class="string">&#x27;Manaphy&#x27;</span>); <span class="comment">-- manaphy</span></span><br><span class="line"><span class="comment">-- upper() 把字符型数据转换为大写的字符</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">upper</span>(<span class="string">&#x27;Manaphy&#x27;</span>); <span class="comment">-- MANAPHY</span></span><br><span class="line"><span class="comment">-- left() 从左边截取字符串</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">left</span>(<span class="string">&#x27;Manaphy&#x27;</span>,<span class="number">3</span>); <span class="comment">-- Man</span></span><br><span class="line"><span class="comment">-- right() 从右边截取字符串</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">right</span>(<span class="string">&#x27;Manaphy&#x27;</span>,<span class="number">3</span>); <span class="comment">-- phy</span></span><br><span class="line"><span class="comment">-- replace() 替换字符串</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">replace</span>(<span class="string">&#x27;一二三四五六七&#x27;</span>,<span class="string">&#x27;四五&#x27;</span>,<span class="string">&#x27;八九&#x27;</span>); <span class="comment">-- 一二三八九六七</span></span><br><span class="line"><span class="comment">-- substr(被截取字段名称,起启下标,截取长度) 截取字符串的函数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">substr</span>(<span class="string">&#x27;一二三四五六七&#x27;</span>,<span class="number">1</span>,<span class="number">3</span>); <span class="comment">-- 一二三</span></span><br><span class="line"><span class="comment">-- reverse() 反转字符串</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">reverse</span>(<span class="string">&#x27;一二三四五六七&#x27;</span>); <span class="comment">-- 七六五四三二一</span></span><br><span class="line"><span class="comment">/* 日期函数 */</span></span><br><span class="line"><span class="comment">-- year(日期字段) 将日期格式化为年(月日时分秒同理)</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(<span class="keyword">now</span>());<span class="comment">-- 输出今年</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(<span class="string">&#x27;2018-01-01&#x27;</span>);<span class="comment">-- 2018</span></span><br><span class="line"><span class="comment">-- 获取当前日期</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">current_date</span>();</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">curdate</span>();</span><br><span class="line"><span class="comment">-- 获取当前日期和时间</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">now</span>();</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">localtime</span>();</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sysdate</span>();</span><br><span class="line"><span class="comment">-- 获取当前时间</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">curtime</span>();</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">current_time</span>();</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="DCL"><a href="#DCL" class="headerlink" title="DCL"></a>DCL</h3><p>Data Control Language 数据控制语言</p>
<ul>
<li>作用:用来定义访问权限和安全级别</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 实体完整性 */</span></span><br><span class="line"><span class="comment">--主键约束 primary key 说明:每个表中要有一个主键, 数据唯一, 且不能为null</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span></span><br><span class="line">);<span class="comment">--创建表时指定主键</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line"><span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">10</span>),</span><br><span class="line">primary <span class="keyword">key</span>(<span class="keyword">id</span>,<span class="keyword">name</span>)</span><br><span class="line">);<span class="comment">--联合主键,创建了两个列作为主键</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line"><span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">10</span>)</span><br><span class="line">);</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> stu <span class="keyword">add</span> primary <span class="keyword">key</span>(<span class="keyword">id</span>);</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> stu <span class="keyword">modify</span> <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>;<span class="comment">--在已经有表的情况下使用</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--唯一约束 unique 说明:数据不能重复</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line"><span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">50</span>) <span class="keyword">unique</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">--自动增长列</span></span><br><span class="line">  <span class="comment">--auto_increment(mysql)</span></span><br><span class="line">  <span class="comment">--identity(sqlserver)</span></span><br><span class="line">  <span class="comment">--sequence(oracle)</span></span><br><span class="line">  <span class="comment">--说明:给主键添加自动增长的数值,列只能是整数类型</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span> auto_increment</span><br><span class="line">);<span class="comment">--mysql的方式</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/* 域完整性 */</span></span><br><span class="line"><span class="comment">--数据类型</span></span><br><span class="line">  <span class="comment">--数值类型:tinyint(1字节) int(4字节) bigint(8字节) folat(4字节) double(8字节) decimal</span></span><br><span class="line">  <span class="comment">--日期类型</span></span><br><span class="line">	date<span class="comment">--1000-01-01/9999-12-31</span></span><br><span class="line">	datetime<span class="comment">--1000-01-01 00:00:00/9999-12-31 23:59:59</span></span><br><span class="line">	timestamp<span class="comment">--1970-01-01 00:00:00/2038 结束时间是第 2147483647(2^31-1)秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07</span></span><br><span class="line">	time<span class="comment">--&#x27;-838:59:59&#x27;/&#x27;838:59:59&#x27; (±35天)</span></span><br><span class="line">	year<span class="comment">--1901/2155</span></span><br><span class="line">  <span class="comment">--字符串类型:char(定长字符串) varchar(变长字符串) binary varbinary text(长文本数据) enum set</span></span><br><span class="line"><span class="comment">--非空约束</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line"><span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="literal">null</span></span><br><span class="line">);</span><br><span class="line"><span class="comment">--默认值约束</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> stu(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">sex <span class="built_in">varchar</span>(<span class="number">2</span>) <span class="keyword">default</span> <span class="string">&#x27;男&#x27;</span></span><br><span class="line">);<span class="comment">--默认是男</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> stu(<span class="keyword">id</span>) <span class="keyword">values</span>(<span class="number">1</span>);<span class="comment">--默认是男</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> stu <span class="keyword">values</span>(<span class="number">2</span>,<span class="keyword">default</span>);<span class="comment">--默认是男</span></span><br><span class="line"><span class="keyword">insert</span> unto stu <span class="keyword">values</span>(<span class="number">3</span>,<span class="literal">null</span>);<span class="comment">--此处是null</span></span><br><span class="line"><span class="comment">--check约束</span></span><br><span class="line"><span class="keyword">check</span>(sex=<span class="string">&#x27;男&#x27;</span> <span class="keyword">or</span>  sex=<span class="string">&#x27;女&#x27;</span>)<span class="comment">--（mysql不支持）</span></span><br><span class="line"><span class="comment">/* 引用完整性 */</span></span><br><span class="line"><span class="comment">--外键约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 左表 <span class="keyword">add</span> <span class="keyword">foreign</span> <span class="keyword">key</span>(左表的某个字段) <span class="keyword">references</span> 右表(主键);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line"><span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line"><span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">classno <span class="built_in">bigint</span>,</span><br><span class="line"><span class="keyword">foreign</span> <span class="keyword">key</span>(classno) <span class="keyword">references</span> tb_class(classno);</span><br><span class="line">  <span class="comment">--注意</span></span><br><span class="line">	<span class="comment">--1.从表的列引用了主表的主键,那么该列就称为外键</span></span><br><span class="line">	<span class="comment">--2.外键的名字可以不相同</span></span><br><span class="line">	<span class="comment">--3.外键的数据类型必须与主表中主键的数据类型完全相同</span></span><br><span class="line">	<span class="comment">--4.外键可以是null值</span></span><br><span class="line"><span class="comment">/* 表与表之间的关系 */</span></span><br><span class="line"><span class="comment">--1.一对多: 一张表中的一条记录再另一张表中出现多次</span></span><br><span class="line"><span class="comment">--2.多对多: 要想两张表之间实现多对多的关系,必须有一张中间表,通过中间表创建与两张表的外键约束,实现多个一	对多,使用这种方式创建外键约束,非常适合实现多个一对多。</span></span><br><span class="line"><span class="comment">--3.一对一:一张表里的记录,在另外一张表里只能出现一次.表和表之间的关系必须通过外键来创建,但是外键一创建就是一对多,因此在从表里将外键设置唯一性约束,实现一对一</span></span><br></pre></td></tr></table></figure>
<h2 id="JDBC"><a href="#JDBC" class="headerlink" title="JDBC"></a>JDBC</h2><p><a target="_blank" rel="noopener" href="https://blog.csdn.net/qq_22172133/article/details/81266048">JDBC使用详解</a><br><a target="_blank" rel="noopener" href="https://www.cnblogs.com/progor/p/9096463.html">JDBC的基本使用</a></p>
<pre><code>JDBC全称Java Database Connectivity
JDBC可以通过载入不同的数据库的“驱动程序”而与不同的数据库进行连接。</code></pre>
<p>JDBC优点:</p>
<ul>
<li>使用的驱动不同，即可连接不同的数据库。</li>
<li>使用同一套操作来操作不同的数据库</li>
<li>如果每一个数据库java都制订一套连接方式，那么当不同的数据库更新的时候，java也需要更新自己的代码，而使用jdbc，使用同一套代码来操作，使用不同的驱动程序（驱动程序由数据库厂商提供）来连接，这使得可以连接不同的数据库。</li>
</ul>
<h2 id="JDBC访问步骤"><a href="#JDBC访问步骤" class="headerlink" title="JDBC访问步骤"></a>JDBC访问步骤</h2><ul>
<li><strong><em>加载并注册数据库驱动（导入驱动包<code>sqljdbc4.jar</code>)</em></strong></li>
<li><strong><em>通过<code>DriverManager</code>获取数据库连接</em></strong></li>
<li><strong><em>通过<code>Connection</code>对象获取<code>Statement</code>对象</em></strong></li>
<li><strong><em>使用Statement执行<code>SQL</code>语句</em></strong></li>
<li><strong><em>操作<code>ResultSet</code>结果集</em></strong></li>
<li><strong><em>关闭资源，释放资源</em></strong></li>
</ul>
<h2 id="导入驱动"><a href="#导入驱动" class="headerlink" title="导入驱动"></a>导入驱动</h2><ul>
<li>在对应的数据库厂商网站获取对应的jar包</li>
<li>将对应的jar包添加到引用。<br>在eclipse中可以将jar包导入一个<code>文件夹</code>后，右键<code>Build Path</code> -&gt; <code>add to path</code>就可以将jar包添加到当前项目引用的库里面。<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/03/33c99dda93535d189da51d0348a48583.png"></li>
</ul>
<h2 id="JDBC实例"><a href="#JDBC实例" class="headerlink" title="JDBC实例"></a>JDBC实例</h2><p>在SQLServer数据库系统创建了一个<code>Student数据库</code>，数据库有一个<code>stu表</code><br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/03/57787c1bb14d5ffc2a0df2170120789a.png"><br>stu表中数据如下：<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/03/181baf3bf37103f87daf6a0fcb93a615.png"></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> JDBC_01;</span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">JDBC_01</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="comment">// TODO Auto-generated method stub</span></span><br><span class="line">		Statement stmt=<span class="keyword">null</span>;</span><br><span class="line">		ResultSet rs=<span class="keyword">null</span>;</span><br><span class="line">		Connection conn=<span class="keyword">null</span>;</span><br><span class="line">		<span class="keyword">try</span> &#123;</span><br><span class="line">			<span class="comment">/*</span></span><br><span class="line"><span class="comment">			* MySQL和SQLServer加载有以下不同</span></span><br><span class="line"><span class="comment">			   1.MySQL加载驱动</span></span><br><span class="line"><span class="comment">			   Class.forName(&quot;com.mysql.jdbc.Driver&quot;);</span></span><br><span class="line"><span class="comment">			   2.通过DriverManager获取数据库连接</span></span><br><span class="line"><span class="comment">			   String url=&quot;jdbc:mysql://localhost:3306/Student?charaterEncoding=utf-8&quot;;</span></span><br><span class="line"><span class="comment">			 */</span></span><br><span class="line"><span class="comment">//*****************************************************************************************88</span></span><br><span class="line">			<span class="comment">//1.加载驱动SQL server</span></span><br><span class="line">			Class.forName(<span class="string">&quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;</span>);</span><br><span class="line">			System.out.println(<span class="string">&quot;1.加载成功&quot;</span>);</span><br><span class="line">			<span class="comment">//2.通过DriverManager获取数据库连接</span></span><br><span class="line">			<span class="comment">//Student为数据库名</span></span><br><span class="line">			String url=<span class="string">&quot;jdbc:sqlserver://localhost:1433;DatabaseName=Student&quot;</span>;</span><br><span class="line">			String username=<span class="string">&quot;sa&quot;</span>;</span><br><span class="line">			String password=<span class="string">&quot;123456&quot;</span>;</span><br><span class="line">			conn=DriverManager.getConnection(url,username,password);</span><br><span class="line">		    System.out.println(<span class="string">&quot;2.连接成功&quot;</span>);</span><br><span class="line">			<span class="comment">//3.通过Connection对象获取Statement对象</span></span><br><span class="line">			stmt=conn.createStatement();</span><br><span class="line">			System.out.println(<span class="string">&quot;3.成功获取Statement对象&quot;</span>);</span><br><span class="line">			<span class="comment">//4.使用Statement执行SQL语句</span></span><br><span class="line">			String sql=<span class="string">&quot;select * from stu&quot;</span>; <span class="comment">//从stu表中查询</span></span><br><span class="line">			rs=stmt.executeQuery(sql);</span><br><span class="line">			System.out.println(<span class="string">&quot;4.执行SQL结果如下&quot;</span>+<span class="string">&quot;\n&quot;</span>);</span><br><span class="line">			<span class="comment">//5.操作ResultSet结果集</span></span><br><span class="line">			System.out.println(<span class="string">&quot;ID|name|sex&quot;</span>);</span><br><span class="line">			<span class="keyword">while</span>(rs.next()) &#123;</span><br><span class="line">				<span class="keyword">int</span> id=rs.getInt(<span class="string">&quot;ID&quot;</span>);</span><br><span class="line">				String name=rs.getString(<span class="string">&quot;name&quot;</span>);</span><br><span class="line">				String sex=rs.getString(<span class="string">&quot;sex&quot;</span>);</span><br><span class="line">				System.out.println(id+<span class="string">&quot;|&quot;</span>+name+<span class="string">&quot;|&quot;</span>+sex+<span class="string">&quot;|&quot;</span>);</span><br><span class="line">			&#125;</span><br><span class="line">		&#125;<span class="keyword">catch</span>(ClassNotFoundException e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">		&#125;<span class="keyword">finally</span> &#123;   <span class="comment">//释放资源，反着关闭</span></span><br><span class="line">			<span class="keyword">if</span>(rs!=<span class="keyword">null</span>) &#123;</span><br><span class="line">				rs.close();</span><br><span class="line">			&#125;</span><br><span class="line">			<span class="keyword">if</span>(stmt!=<span class="keyword">null</span>) &#123;</span><br><span class="line">				stmt.close();</span><br><span class="line">			&#125;</span><br><span class="line">			<span class="keyword">if</span>(conn!=<span class="keyword">null</span>) &#123;</span><br><span class="line">				conn.close();</span><br><span class="line">			&#125;</span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>执行结果如下：<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/03/b0204f27e029fadb8fdaddc30cfd035b.png"></p>
<h2 id="详细介绍"><a href="#详细介绍" class="headerlink" title="详细介绍"></a>详细介绍</h2><ul>
<li><p><strong>加载数据库驱动——Driver接口</strong><br>Driver接口由数据库厂家提供，作为java开发人员，只需要使用Driver接口就可以了。在编程中要连接数据库，必须先装载特定厂商的数据库驱动程序，不同的数据库有不同的装载方法。</p>
<pre><code>  Class.forName(&quot;com.mysql.jdbc.Driver&quot;); //MySQL
  Class.forName(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;); //SQLServer
  Class.forName(&quot;oracle.jdbc.driver.OracleDriver&quot;); //Oracle</code></pre>
</li>
<li><p><strong>建立连接——Connection接口</strong><br>客户端与数据库所有交互都是通过<code>connection对象</code>完成的<br>使用DriverManager来获取<br><code>URL</code>用于标识数据库的位置，程序员通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：</p>
<pre><code>  String url=jdbc:mysql:［］//localhost:3306/数据库名 ?参数名：参数值
  url=jdbc:oracle:thin:@localhost:1521:database   //Oracle
  url=jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=database //SQLServer
      url=jdbc:mysql://localhost:3306/database   //MySQL
  Connection conn = DriverManager.getConnection(url,user,pass); </code></pre>
</li>
</ul>
<table>
<thead>
<tr>
<th align="center">DriverManager类常用方法</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center"><strong>registerDriver(Driver driver)</strong></td>
<td align="center">用于向DriverManager中注册给定的JDBC驱动程序</td>
</tr>
<tr>
<td align="center"><strong>getConnection(String url,String user,String password)</strong></td>
<td align="center">用于建立和数据库的连接，并返回表示连接的Connction对象</td>
</tr>
</tbody></table>
<hr>
<table>
<thead>
<tr>
<th>Connection常用方法</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td><strong>createStatement()</strong></td>
<td>创建向数据库发送sql的statement对象。</td>
</tr>
<tr>
<td><strong>prepareStatement(sql)</strong></td>
<td>创建向数据库发送预编译sql的PrepareSatement对象。</td>
</tr>
<tr>
<td>prepareCall(sql)</td>
<td>创建执行存储过程的callableStatement对象。</td>
</tr>
<tr>
<td>setAutoCommit(boolean autoCommit)</td>
<td>设置事务是否自动提交。</td>
</tr>
<tr>
<td>commit()</td>
<td>在链接上提交事务。</td>
</tr>
<tr>
<td>rollback()</td>
<td>在此链接上回滚事务。</td>
</tr>
<tr>
<td>getMetaData()</td>
<td>用于返回表示数据库的元数据的DatabaseMetaData对象</td>
</tr>
</tbody></table>
<ul>
<li><strong>执行SQL语句——Statement接口</strong><br>用于执行静态SQL语句并返回它所生成结果的对象。<br>三种Statement类：</li>
</ul>
<ol>
<li> <strong>Statement</strong>：由createStatement创建，用于发送简单的SQL语句（不带参数）。</li>
<li> <strong>PreparedStatement</strong> ：继承自Statement接口，由preparedStatement创建，用于发送含有一个或多个参数的SQL语句。PreparedStatement对象比Statement对象的效率更高，并且可以防止SQL注入，所以我们一般都使用PreparedStatement。</li>
<li> <strong>CallableStatement</strong>：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。</li>
</ol>
<pre><code>    Statement st = conn.createStatement(); //获取用于向数据库发送sql语句的statement
    StiString sql=&quot;select * from stu&quot;;
    st.executeQuery(sql);//向数据库发送sql</code></pre>
<table>
<thead>
<tr>
<th>Statement对象常用方法</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>executeQuery(String sql)</td>
<td>用于向数据发送查询语句,返回ResultSet结果集。</td>
</tr>
<tr>
<td>executeUpdate(String sql)</td>
<td>用于向数据库发送insert、update或delete语句,返回更新的行数</td>
</tr>
<tr>
<td>execute(String sql)</td>
<td>用于向数据库发送任意sql语句  ,返回是否有结果集</td>
</tr>
<tr>
<td>addBatch(String sql)</td>
<td>把多条sql语句放到一个批处理中。</td>
</tr>
<tr>
<td>executeBatch()</td>
<td>向数据库发送一批sql语句执行。</td>
</tr>
</tbody></table>
<p>  PreperedStatement是Statement的孩子，它的实例对象可以通过调用：</p>
<pre><code>    PreperedStatement st = conn.preparedStatement()
    StiString sql=&quot;select * from users where name=? and password=?&quot;&quot;;
    st=conn.preparedStatement(sql);
    st.setString(2,password);
    st.executeQuery();</code></pre>
<ul>
<li><p>Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement 可对SQL进行预编译，从而提高数据库的执行效率。</p>
</li>
<li><p>并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。</p>
</li>
<li><p>** 获取结果——ResultSet接口**<br>Jdbc程序中的<code>ResultSet</code>用于代表Sql语句的执行结果。Resultset封装执行结果时，采用的类似于表格的方式，ResultSet  对象维护了一个<code>指向表格数据行的游标</code>，初始的时候，游标在第一行之前，调用<code>ResultSet.next()  </code>方法，可以使游标指向具体的数据行，进行调用方法获取该行的数据。</p>
</li>
</ul>
<p>1.<strong>获取行</strong><br>ResultSet提供了对结果集进行滚动的方法：</p>
<pre><code>    next()：移动到下一行
    Previous()：移动到前一行
    absolute(int row)：移动到指定行
    beforeFirst()：移动resultSet的最前面。
    afterLast() ：移动到resultSet的最后面。</code></pre>
<p>2.<strong>获取值</strong><br>ResultSet既然用于封装执行结果的，所以该对象提供的都是用于获取数据的get方法：</p>
<ul>
<li>获取任意类型的数据<pre><code>  getObject(int index)
  getObject(string columnName)</code></pre>
</li>
<li>获取指定类型的数据，例如：<pre><code>  getString(int index)
  getString(String columnName)</code></pre>
</li>
</ul>
<table>
<thead>
<tr>
<th>SQL类型</th>
<th>JDBC对应方法</th>
<th>返回类型</th>
</tr>
</thead>
<tbody><tr>
<td>bit(1)，bit(n)</td>
<td>getBoolean，getBytes()</td>
<td>Boolean，byte[]</td>
</tr>
<tr>
<td>tinyint</td>
<td>getByte()</td>
<td>Byte</td>
</tr>
<tr>
<td>smallint</td>
<td>getShort()</td>
<td>Short</td>
</tr>
<tr>
<td>int</td>
<td>getInt</td>
<td>Int</td>
</tr>
<tr>
<td>bigint</td>
<td>getLong()</td>
<td>Long</td>
</tr>
<tr>
<td>char,varchar,longvarchar</td>
<td>getString</td>
<td>String</td>
</tr>
<tr>
<td>text(clob) blob</td>
<td>getClob()，getblob()</td>
<td>Clob，blob</td>
</tr>
<tr>
<td>date</td>
<td>getDate()</td>
<td>java.sql.Date</td>
</tr>
<tr>
<td>time</td>
<td>getTime()</td>
<td>java.sql.Time</td>
</tr>
<tr>
<td>timestamp</td>
<td>getTimestamp</td>
<td>java.sql.Timestamp</td>
</tr>
</tbody></table>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">小李博客</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://xiaoliblog.cn">https://xiaoliblog.cn</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://xiaoliblog.cn" target="_blank">小李博客</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/JavaSE/">JavaSE</a></div><div class="post_share"><div class="social-share" data-image="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button button--animated"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/wechat.png" target="_blank"><img class="post-qr-code-img" src="/img/wechat.png" alt="微信"/></a><div class="post-qr-code-desc">微信</div></li><li class="reward-item"><a href="/img/alipay.png" target="_blank"><img class="post-qr-code-img" src="/img/alipay.png" alt="支付宝"/></a><div class="post-qr-code-desc">支付宝</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/page/vercel.html"><img class="prev-cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/12/edf0a6a94fd1fe224bb18bb517063e1b.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">使用Vercel加速GitHub Pages</div></div></a></div><div class="next-post pull-right"><a href="/page/java09.html"><img class="next-cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">Java👉事件处理</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/page/java01.html" title="Java👉IO流"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-05</div><div class="title">Java👉IO流</div></div></a></div><div><a href="/page/java06.html" title="Java👉泛型和枚举"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-05</div><div class="title">Java👉泛型和枚举</div></div></a></div><div><a href="/page/java12.html" title="Java👉异常机制"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-05</div><div class="title">Java👉异常机制</div></div></a></div><div><a href="/page/java09.html" title="Java👉事件处理"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-05</div><div class="title">Java👉事件处理</div></div></a></div><div><a href="/page/java11.html" title="Java👉线程、线程通信"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-05</div><div class="title">Java👉线程、线程通信</div></div></a></div><div><a href="/page/java05.html" title="Java👉注解和反射"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/30/0b4418a37c4d8fd66e1607c74793c6be.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-05</div><div class="title">Java👉注解和反射</div></div></a></div></div></div><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="twikoo-wrap"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">小李博客</div><div class="author-info__description">越努力，越幸运！</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div><div class="card-info-data-item is-center"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xiaoliblog"><i class="fab fa-github"></i><span>博主的GitHub首页</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://gitee.com/xiaoliblog" target="_blank" title="Gitee"><i class="iconfont icon-gitee card_icon_gitee"></i></a><a class="social-icon" href="https://space.bilibili.com/390969485" target="_blank" title="BiliBili"><i class="iconfont icon-bilibili card_icon_bilibili"></i></a><a class="social-icon" href="http://wpa.qq.com/msgrd?v=3&amp;uin=2312057536&amp;site=CSDN&amp;menu=yes" target="_blank" title="QQ"><i class="iconfont icon-qq card_icon_qq"></i></a><a class="social-icon" href="https://github.com/xiaoliblog" target="_blank" title="GitHub"><i class="iconfont icon-git card_icon_git"></i></a><a class="social-icon" href="https://blog.csdn.net/qq_43266250?spm=1010.2135.3001.5113" target="_blank" title="CSDN"><i class="iconfont icon-csdn card_icon_csdn"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">正在考研备考中💦</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.</span> <span class="toc-text">数据库连接</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%BF%B0"><span class="toc-number">1.1.</span> <span class="toc-text">数据库概述</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#SQL%E8%AF%AD%E8%A8%80"><span class="toc-number">1.2.</span> <span class="toc-text">SQL语言</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#DDL"><span class="toc-number">1.2.1.</span> <span class="toc-text">DDL</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#DML"><span class="toc-number">1.2.2.</span> <span class="toc-text">DML</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#DQL"><span class="toc-number">1.2.3.</span> <span class="toc-text">DQL</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0"><span class="toc-number">1.2.4.</span> <span class="toc-text">常用函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#DCL"><span class="toc-number">1.2.5.</span> <span class="toc-text">DCL</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#JDBC"><span class="toc-number">1.3.</span> <span class="toc-text">JDBC</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#JDBC%E8%AE%BF%E9%97%AE%E6%AD%A5%E9%AA%A4"><span class="toc-number">1.4.</span> <span class="toc-text">JDBC访问步骤</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%AF%BC%E5%85%A5%E9%A9%B1%E5%8A%A8"><span class="toc-number">1.5.</span> <span class="toc-text">导入驱动</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#JDBC%E5%AE%9E%E4%BE%8B"><span class="toc-number">1.6.</span> <span class="toc-text">JDBC实例</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%AF%A6%E7%BB%86%E4%BB%8B%E7%BB%8D"><span class="toc-number">1.7.</span> <span class="toc-text">详细介绍</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/page/project01.html" title="前端实例🥳响应式网站首页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@c19917500ab083c77c7613263ba7ee74d5a08ae6/2021/04/30/469f30b141d73fa0fc4c962662d5813f.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="前端实例🥳响应式网站首页"/></a><div class="content"><a class="title" href="/page/project01.html" title="前端实例🥳响应式网站首页">前端实例🥳响应式网站首页</a><time datetime="2021-04-30T11:50:53.094Z" title="发表于 2021-04-30 19:50:53">2021-04-30</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/WeChatpay.html" title="微信支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@f2f670b92ea149650ffa7834354fc90284f2f44a/2021/04/29/5bdc9b381a06193d27cf2fb7c2fb608a.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="微信支付对接"/></a><div class="content"><a class="title" href="/page/WeChatpay.html" title="微信支付对接">微信支付对接</a><time datetime="2021-04-29T12:20:48.070Z" title="发表于 2021-04-29 20:20:48">2021-04-29</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Alipay.html" title="支付宝支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61a9b6a6e09e4bda38bb08e3104b717885beaee5/2021/04/29/c3fa51f9cf14e90d9e5a7aa8814dd041.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="支付宝支付对接"/></a><div class="content"><a class="title" href="/page/Alipay.html" title="支付宝支付对接">支付宝支付对接</a><time datetime="2021-04-27T16:00:00.000Z" title="发表于 2021-04-28 00:00:00">2021-04-28</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Pagination+PageHelper实现分页"/></a><div class="content"><a class="title" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页">Vue.js前端框架🎯Pagination+PageHelper实现分页</a><time datetime="2021-04-26T14:48:39.701Z" title="发表于 2021-04-26 22:48:39">2021-04-26</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Vue-Element-admin模版"/></a><div class="content"><a class="title" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版">Vue.js前端框架🎯Vue-Element-admin模版</a><time datetime="2021-04-19T16:00:00.000Z" title="发表于 2021-04-20 00:00:00">2021-04-20</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By 小李博客</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">🎯Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">🎉Butterfly</a></div><div class="footer_custom_text"><span><a style="margin-inline:5px" target="_blank" href="https://hexo.io/"><img src="https://img.shields.io/badge/Frame-Hexo-blue?style=flat&logo=hexo" title="博客框架为Hexo"></a><a style="margin-inline:5px" target="_blank" href="https://butterfly.js.org/"><img src="https://img.shields.io/badge/Theme-Butterfly-6513df?style=flat&logo=bitdefender" title="主题采用butterfly"></a><a style="margin-inline:5px" target="_blank" href="https://www.jsdelivr.com/"><img src="https://img.shields.io/badge/CDN-jsDelivr-orange?style=flat&logo=jsDelivr" title="本站使用JsDelivr为静态资源提供CDN加速"></a><a style="margin-inline:5px" target="_blank" href="https://vercel.com/ "><img src="https://img.shields.io/badge/Hosted-Vercel-brightgreen?style=flat&logo=Vercel" title="本站采用双线部署，默认线路托管于Vercel"></a><a style="margin-inline:5px" target="_blank" href="https://coding.net/ "><img src="https://img.shields.io/badge/Hosted-Coding-0cedbe?style=flat&logo=Codio" title="本站采用双线部署，联通线路托管于Coding"></a><a style="margin-inline:5px" target="_blank" href="https://github.com/"><img src="https://img.shields.io/badge/Source-Github-d021d6?style=flat&logo=GitHub" title="本站项目由Gtihub托管"></a><a style="margin-inline:5px" target="_blank" href="http://creativecommons.org/licenses/by-nc-sa/4.0/"><img src="https://img.shields.io/badge/Copyright-BY--NC--SA%204.0-d42328?style=flat&logo=Claris" title="本站采用知识共享署名-非商业性使用-相同方式共享4.0国际许可协议进行许可"></a></span></div><div class="icp"><a target="_blank" rel="noopener" href="https://beian.miit.gov.cn/"><img class="icp-icon" src="/img/icp.png" alt="ICP"/><span>湘ICP备2021002541号</span></a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="font-plus" type="button" title="放大字体"><i class="fas fa-plus"></i></button><button id="font-minus" type="button" title="缩小字体"><i class="fas fa-minus"></i></button><button id="translateLink" type="button" title="简繁转换">繁</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="chat_btn" type="button" title="rightside.chat_btn"><i class="fas fa-sms"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="algolia-search"><div class="search-dialog"><div class="search-dialog__title" id="algolia-search-title">Algolia</div><div id="algolia-input-panel"><div id="algolia-search-input"></div></div><hr/><div id="algolia-search-results"><div id="algolia-hits"></div><div id="algolia-pagination"></div><div id="algolia-stats"></div></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="https://cdn.jsdelivr.net/npm/vue@2.6.11"></script><script src="/js/main.js"></script><script defer src="/js/tw_cn.js"></script><script defer src="https://cdn.jsdelivr.net/npm/medium-zoom/dist/medium-zoom.min.js"></script><script src="https://cdn.jsdelivr.net/npm/instant.page/instantpage.min.js" type="module" defer></script><script defer src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script defer src="/js/search/algolia.js"></script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    loader: {
      source: {
        '[tex]/amsCd': '[tex]/amscd'
      }
    },
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        addClass: [200,() => {
          document.querySelectorAll('mjx-container:not([display=\'true\']').forEach( node => {
            const target = node.parentNode
            if (!target.classList.contains('has-jax')) {
              target.classList.add('mathjax-overflow')
            }
          })
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typeset()
}</script><script>if (document.getElementsByClassName('mermaid').length) {
  if (window.mermaidJsLoad) mermaid.init()
  else {
    getScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(() => {
      window.mermaidJsLoad = true
      mermaid.initialize({
        theme: 'default',
      })
      false && mermaid.init()
    })
  }
}</script><script>(()=>{
  const $countDom = document.getElementById('twikoo-count')
  const init = () => {
    twikoo.init(Object.assign({
      el: '#twikoo-wrap',
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: ''
    }, null))
  }

  const getCount = () => {
    twikoo.getCommentsCount({
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: '',
      urls: [window.location.pathname],
      includeReply: false
    }).then(function (res) {
      $countDom.innerText = res[0].count
    }).catch(function (err) {
      console.error(err);
    });
  }

  const loadTwikoo = (bool = false) => {
    if (typeof twikoo === 'object') {
      init()
      bool && $countDom && setTimeout(getCount,0)
    } else {
      getScript('https://cdn.jsdelivr.net/npm/twikoo@1.3.0/dist/twikoo.all.min.js').then(()=> {
        init()
        bool && $countDom && setTimeout(getCount,0)
      })
    }
  }

  if ('Twikoo' === 'Twikoo' || !true) {
    if (true) btf.loadComment(document.getElementById('twikoo-wrap'), loadTwikoo)
    else loadTwikoo(true)
  } else {
    window.loadOtherComment = () => {
      loadTwikoo()
    }
  }
})()</script></div><script defer src="//lib.baomitu.com/jquery/3.5.1/jquery.min.js"></script><script defer src="https://myhkw.cn/api/player/160561664166" id="myhk" key="160561664166" m="1"></script><div><canvas id="snow" style="position:fixed;top:0;left:0;width:100%;height:100%;z-index:99999;pointer-events:none"></canvas></div><script>const notMobile = (!(navigator.userAgent.match(/(phone|pad|pod|iPhone|iPod|ios|iPad|Android|Mobile|BlackBerry|IEMobile|MQQBrowser|JUC|Fennec|wOSBrowser|BrowserNG|WebOS|Symbian|Windows Phone)/i)));</script><scrip async type="text/javascript" src="https://cdn.jsdelivr.net/gh/Candinya/Kratos-Rebirth@latest/source/js/snow.min.js"></scrip><scrip defer src="https://cdn.jsdelivr.net/npm/hexo-theme-volantis@latest/source/js/issues.min.js"></scrip><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><script>var gitcalendar = new Vue({
  el: '#gitcalendar',
  data: {
    simplemode: true, 
    user: 'xiaoliblog',
    fixed: 'fixed',
    px: 'px',
    x: '',
    y: '',
    span1: '',
    span2: '',
    month: ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'],
    monthchange: [],
    oneyearbeforeday: '',
    thisday: '',
    amonthago: '',
    aweekago: '',
    weekdatacore: 0,
    datacore: 0,
    total: 0,
    datadate: '',
    data: [],
    positionplusdata: [],
    firstweek: [],
    lastweek: [],
    beforeweek: [],
    thisweekdatacore: 0,
    mounthbeforeday: 0,
    mounthfirstindex: 0,
    crispedges: 'crispedges',
    thisdayindex: 0,
    amonthagoindex: 0,
    amonthagoweek: [],
    firstdate: [],
    first2date: [],
    montharrbefore: [],
    monthindex: 0,
    color: ['#ebedf0', '#f1f8ff', '#dbedff', '#c8e1ff', '#79b8ff', '#2188ff', '#0366d6', '#005cc5', '#044289', '#032f62', '#05264c']
  },
  methods: {
    selectStyle(data, event) {
      document.querySelector('.angle-wrapper').style.display = 'block'
      this.span1 = data.date;
      this.span2 = data.count;
      this.x = event.clientX - 100;
      this.y = event.clientY - 60
    },
    outStyle() {
      document.querySelector('.angle-wrapper').style.display = 'none'
    },
    thiscolor(x) {
      if (x === 0) {
        let i = parseInt(x / 2);
        return this.color[0]
      } else if (x < 2) {
        return this.color[1]
      } else if (x < 20) {
        let i = parseInt(x / 2);
        return this.color[i]
      } else {
        return this.color[9]
      }
    },
  }
});
var apiurl = 'python-github-calendar-api-ruby.vercel.app' ? 'https://python-github-calendar-api-ruby.vercel.app/api?' : 'https://githubapi.ryanchristian.dev/user/'
var githubapiurl = apiurl + gitcalendar.user;
//canvas绘图
function responsiveChart() {
  let c = document.getElementById("gitcanvas");
  if (c) {
    let cmessage = document.getElementById("gitmessage");
    let ctx = c.getContext("2d");
    c.width = document.getElementById("gitcalendarcanvasbox").offsetWidth;
    let linemaxwitdh = 0.96 * c.width / gitcalendar.data.length;
    c.height = 9 * linemaxwitdh;
    let lineminwitdh = 0.8 * linemaxwitdh;
    let setposition = {
      x: 0.02 * c.width,
      y: 0.025 * c.width
    };
    for (let week in gitcalendar.data) {
      weekdata = gitcalendar.data[week];
      for (let day in weekdata) {
        let dataitem = {
          date: "",
          count: "",
          x: 0,
          y: 0
        };
        gitcalendar.positionplusdata.push(dataitem);
        ctx.fillStyle = gitcalendar.thiscolor(weekdata[day].count);
        setposition.y = Math.round(setposition.y * 100) / 100;
        dataitem.date = weekdata[day].date;
        dataitem.count = weekdata[day].count;
        dataitem.x = setposition.x;
        dataitem.y = setposition.y;
        ctx.fillRect(setposition.x, setposition.y, lineminwitdh, lineminwitdh);
        setposition.y = setposition.y + linemaxwitdh
      };
      setposition.y = 0.025 * c.width;
      setposition.x = setposition.x + linemaxwitdh
    };
    ctx.font = "600  Arial";
    ctx.fillStyle = '#aaa';
    ctx.fillText("日", 0, 1.9 * linemaxwitdh);
    ctx.fillText("二", 0, 3.9 * linemaxwitdh);
    ctx.fillText("四", 0, 5.9 * linemaxwitdh);
    ctx.fillText("六", 0, 7.9 * linemaxwitdh);
    let monthindexlist = c.width / 24;
    for (let index in gitcalendar.monthchange) {
      ctx.fillText(gitcalendar.monthchange[index], monthindexlist, 0.7 * linemaxwitdh);
      monthindexlist = monthindexlist + c.width / 12
    };
    cmessage.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
    };
    c.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
      getMousePos(c, event);
    };

    function getMousePos(canvas, event) {
      var rect = canvas.getBoundingClientRect();
      var x = event.clientX - rect.left * (canvas.width / rect.width);
      var y = event.clientY - rect.top * (canvas.height / rect.height);
      //console.log("x:"+x+",y:"+y);
      for (let item of gitcalendar.positionplusdata) {
        let lenthx = x - item.x;
        let lenthy = y - item.y;
        //console.log(lenthx,lenthy);
        if (0 < lenthx && lenthx < lineminwitdh) {
          if (0 < lenthy && lenthy < lineminwitdh) {
            //console.log(item.date,item.count)
            document.querySelector('.angle-wrapper').style.display = 'block'
            gitcalendar.span1 = item.date;
            gitcalendar.span2 = item.count;
            gitcalendar.x = event.clientX - 100;
            gitcalendar.y = event.clientY - 60
          }
        }
        //if(0< x - item.x <lineminwitdh&&0< y - item.y <lineminwitdh){
        //console.log(item.count,item.date);
        //}
      }
    }
  }
}
//数据统计算法
function addlastmonth() {
  if (gitcalendar.thisdayindex === 0) {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweekcore(48);
    gitcalendar.thisweekdatacore += gitcalendar.firstdate[6].count;
    gitcalendar.amonthago = gitcalendar.firstdate[6].date
  } else {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweek2core();
    gitcalendar.amonthago = gitcalendar.first2date[gitcalendar.thisdayindex - 1].date
  }
};

function thisweek2core() {
  for (let i = gitcalendar.thisdayindex - 1; i < gitcalendar.first2date.length; i++) {
    gitcalendar.thisweekdatacore += gitcalendar.first2date[i].count
  }
};

function thisweekcore(index) {
  for (let item of gitcalendar.data[index]) {
    gitcalendar.thisweekdatacore += item.count
  }
};

function addlastweek() {
  for (let item of gitcalendar.lastweek) {
    gitcalendar.weekdatacore += item.count
  }
};

function addbeforeweek() {
  for (let i = gitcalendar.thisdayindex; i < gitcalendar.beforeweek.length; i++) {
    gitcalendar.weekdatacore += gitcalendar.beforeweek[i].count
  }
};

function addweek(data) {
  if (gitcalendar.thisdayindex === 6) {
    gitcalendar.aweekago = gitcalendar.lastweek[0].date;
    addlastweek()
  } else {
    lastweek = data.contributions[51];
    gitcalendar.aweekago = lastweek[gitcalendar.thisdayindex + 1].date;
    addlastweek();
    addbeforeweek()
  }
}

fetch(githubapiurl)
  .then(data => data.json())
  .then(data => {
    gitcalendar.data = data.contributions;
    gitcalendar.total = data.total;
    gitcalendar.first2date = gitcalendar.data[48];
    gitcalendar.firstdate = gitcalendar.data[47];
    gitcalendar.firstweek = data.contributions[0];
    gitcalendar.lastweek = data.contributions[52];
    gitcalendar.beforeweek = data.contributions[51];
    gitcalendar.thisdayindex = gitcalendar.lastweek.length - 1;
    gitcalendar.thisday = gitcalendar.lastweek[gitcalendar.thisdayindex].date;
    gitcalendar.oneyearbeforeday = gitcalendar.firstweek[0].date;
    gitcalendar.monthindex = gitcalendar.thisday.substring(5, 7) * 1;
    gitcalendar.montharrbefore = gitcalendar.month.splice(gitcalendar.monthindex, 12 - gitcalendar.monthindex);
    gitcalendar.monthchange = gitcalendar.montharrbefore.concat(gitcalendar.month);
    addweek(data);
    addlastmonth();
    responsiveChart();
  })
  .catch(function(error) {
    console.log(error);
  });

//手机版更换为svg绘制
if (document.getElementById("gitcalendarcanvasbox").offsetWidth < 500) {
  gitcalendar.simplemode = false
}

//当改变窗口大小时重新绘制canvas
window.onresize = function() {
  if (gitcalendar.simplemode) responsiveChart()
}

//解决滚动滑轮时出现的标签显示
window.onscroll = function() {
  if (document.querySelector('.angle-wrapper')) {
    document.querySelector('.angle-wrapper').style.display = 'none'
  }
};</script></div><script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginRootPath":"live2dw/","pluginJsPath":"lib/","pluginModelPath":"assets/","tagMode":false,"log":false,"model":{"jsonPath":"/live2dw/assets/hijiki.model.json"},"display":{"position":"right","width":150,"height":300},"mobile":{"show":true},"react":{"opacity":0.7}});</script></body></html>